-- drop function if exists readbowtie(text, text);

create or replace function readbowtie(infile text, dest_table text)
RETURNS VOID AS $$

----------------------[ readbowtie(infile, dest_table) ]-----------------------
--
-- Import the output file of bowtie alignment produced in standard format.
--
-- Usage:
-- select readbowtie('C:/path/to/file', 'name of destination table')
-- Use forward slahes in the path to file also on Windows 

-- See also http://bowtie-bio.sourceforge.net/manual.shtml
-------------------------------------------------------------------------------
          
  BEGIN
  -- Create destination table
  EXECUTE 
  'create table ' || dest_table || '(
    read_name varchar,
    strand varchar(1),
    refseq_name varchar,
    position bigint,
    read_sequence varchar,
    read_quality varchar,
    no_inst int,
    list_mism varchar   
    )';
  -------------------------[ Add some comments ]-------------------------------
  EXECUTE
  'COMMENT ON COLUMN ' || dest_table || '.refseq_name IS 
    ''Name of reference sequence where alignment occurs, or ordinal ID if no name was provided''';
  EXECUTE
  'COMMENT ON COLUMN ' || dest_table || '.position IS 
    ''0-based offset into the forward reference strand where leftmost 
    character of the alignment occurs''';
  EXECUTE
  'COMMENT ON COLUMN ' || dest_table || '.read_sequence IS 
    ''Read sequence (reverse-complemented if orientation is -)''';
  EXECUTE
  'COMMENT ON COLUMN ' || dest_table || '.read_quality IS 
    ''ASCII-encoded read qualities (reversed if orientation is -). 
    The quality values on Phred scale in ASCII-offset by 33 (ASCII char !)''';
  EXECUTE
  'COMMENT ON COLUMN ' || dest_table || '.no_inst IS 
    ''Number of other instances where the same read aligns against the same 
    reference characters as were aligned against in this alignment. This is 
    not the number of other places the read aligns with the same number of 
    mismatches. The number in this column is generally not a good proxy for 
    that number (e.g., the number in this column may be ''''0'''' while the 
    number of other alignments with the same number of mismatches might be large).
    This column was previously described as "Reserved"''';
  EXECUTE
  'COMMENT ON COLUMN ' || dest_table || '.list_mism IS 
    ''Comma-separated list of mismatch descriptors. If there are no mismatches in 
    the alignment, this field is empty. A single descriptor has the format 
    offset:reference-base>read-base. The offset is expressed as a 0-based offset 
    from the high-quality (5'''') end of the read.''';

  -------------------------[ Import result file]-------------------------------
  EXECUTE
  'COPY ' || dest_table || ' FROM ''' || infile || E''' WITH CSV DELIMITER E''\t''';

  END;
$$ LANGUAGE 'plpgsql' VOLATILE;